package br.ucb.cesbdao.persistencia.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import br.ucb.cesbdao.beans.Acesso;
import br.ucb.cesbdao.persistencia.AbstractConnectionDAO;
import br.ucb.cesbdao.persistencia.factory.FactoryConnection;


public class AcessoDAO extends AbstractConnectionDAO{
	private static String sqlPrimeiro = "SELECT  *FROM daoAcesso;";
	private static String sqlConsultaNome = "SELECT a.cpf,p.nomePessoa FROM daoAcesso a,daoTrabalhador p WHERE a.cpf = p.cpf AND cpf <> 00000000000 AND nome LIKE ";

	public AcessoDAO(Connection conexao) {
		super(conexao);
	}

	public int addAcesso(Acesso acesso) throws SQLException, ClassNotFoundException {
		String SQL_INSERT = "INSERT INTO daoAcesso VALUES(?,?)";
		PreparedStatement instrucao = getConexao().prepareStatement(SQL_INSERT);
		instrucao.setString(1, acesso.getCpf());
		instrucao.setString(2, acesso.getSenha());
		int retorno = instrucao.executeUpdate();
		instrucao.close();
		return retorno;
	}

	public int deletaAcesso(String cpf) throws SQLException, ClassNotFoundException {
		String SQL_DELETA = "DELETE FROM daoAcesso WHERE cpf= ?";
		PreparedStatement instrucao = getConexao().prepareStatement(SQL_DELETA);
		instrucao.setString(1, cpf);
		int retorno = instrucao.executeUpdate(SQL_DELETA);
		instrucao.close();
		return retorno;
	}

	public Acesso procuraAcessoCEB(String cpf) throws ClassNotFoundException, SQLException {
		String sqlConsultaAcesso = "SELECT * FROM daoAcesso WHERE cpf = "+cpf;
		PreparedStatement ps = getConexao().prepareStatement(sqlConsultaAcesso);
		ResultSet rs = ps.executeQuery(sqlConsultaAcesso);
		Acesso acesso = null;
		if(rs.first())
			acesso = new Acesso(rs.getString("cpf"),rs.getString("senha"));
		ps.close();
		rs.close();
		return acesso;
	}

	public ArrayList<Acesso> consultaAcesso(String nome) throws ClassNotFoundException, SQLException {
		PreparedStatement ps = getConexao().prepareStatement(sqlConsultaNome);
		sqlConsultaNome = sqlConsultaNome+"'%"+nome+"'%";
		ResultSet rs = ps.executeQuery(sqlConsultaNome);
		ArrayList<Acesso> acessos = new ArrayList<Acesso>();
		while(rs.next())
			acessos.add(new Acesso(rs.getString("cpf"),rs.getString("senha")));
		ps.close();
		rs.close();
		return acessos;
	}

	public Acesso consultaAcessoCpf(String cpf) throws ClassNotFoundException, SQLException {
		String sqlConsulta = "SELECT * FROM daoAcesso WHERE cpf = ";
		PreparedStatement ps = getConexao().prepareStatement(sqlConsulta);
		ResultSet rs = ps.executeQuery(sqlConsulta+cpf);
		Acesso acesso = null;
		if(rs.first())
			acesso = new Acesso(rs.getString("cpf"),rs.getString("senha"));
		ps.close();
		rs.close();
		return acesso;
	}

	public int alteraAcesso(Acesso acesso) throws ClassNotFoundException, SQLException {
		String SQL_ALTERA = "UPDATE daoAcesso SET senha = ? WHERE cpf = ?";
		PreparedStatement instrucao = getConexao().prepareStatement(SQL_ALTERA);
		instrucao.setString(1, acesso.getSenha());
		instrucao.setString(2, acesso.getCpf());
		int retorno = instrucao.executeUpdate();
		instrucao.close();
		return retorno;
	}
	
	public boolean verificaPrimeiroAcesso() throws ClassNotFoundException, SQLException {
		PreparedStatement ps = getConexao().prepareStatement(sqlPrimeiro);
		ResultSet rs = ps.executeQuery();
		if(rs.first()){
			FactoryConnection.getInstance().closeConexao(ps, rs);
			return false;
		}
		FactoryConnection.getInstance().closeConexao(ps, rs);
		return true;
	}

	public void criaBanco(String[] sql) throws ClassNotFoundException, SQLException {
		Connection con = FactoryConnection.getInstance().criaConexaoFirst();
		Statement st = con.createStatement();
		for(int i = 0; i<sql.length; i++)  
            if(!sql[i].trim().equals(""))  
                st.execute(sql[i]);  
	}

}
